<?php
/**
 * 
 * pdo db class
 * @author zhupp
 * @version 20120418 22:05
 * @example
 * require_once './DB.php';
	 $rw = array(
			'r'=> array(
				array('host'=>'localhost', 'port'=>'3306', 'username'=>'root', 'password'=>'hcl1355'),
				array('host'=>'192.168.3.94', 'port'=>'3306', 'username'=>'root', 'password'=>'123'),
			),
			
			'w'=>array(
				array('host'=>'localhost', 'port'=>'3306', 'username'=>'root', 'password'=>'hcl1355'),
				array('host'=>'192.168.3.94', 'port'=>'3306', 'username'=>'root', 'password'=>'123'),
			),
			'memcache'=>array(
				array('host'=>'127.0.0.1', 'port'=>'11211'),
				array('host'=>'192.168.3.106', 'port'=>'11211'),
			),
	);
 * DB::init($rw);
 * select:
 * $query = DB::fetch('SELECT * FROM `database`.`table` WHERE `id`=?', array(2));
 * insert/update/delete:
 * $insert = DB::execute('INSERT INTO `database`.`table` ('username') VALUES (?)', array('zhupp'));
 */
class DB {
	/**
	 * 
	 * PDO实例
	 * @var object
	 */
    private static $db;
    
    /**
     * 是否使用缓存
     * @var boolen
     */
	private static $cache = false;
	
    /**
     * 
     * PDO警告模式 默认为EXCEPTION模式 发布时可置为PDO::ERRMODE_SILENT(静默模式)
     * @var PHP CONST $errMode 
     */
    private static $errMode = PDO::ERRMODE_EXCEPTION;
    
    /**
     * 
     * 最后一条SQL语句
     * @var unknown_type
     */
	private static $lastSql;
	
	/**
	 * 危险的sql语句列表
	 */
	private static $danger = array(
		'drop[\s]+table',
		'drop[\s]+database',
	);
	
    /**
     * 私有化clone防止拷贝
     */
    private function __clone() {}
        
    /**
     * 私有化构造方法防止外界实例化对象
     */
    private function __construct() {}
	
    /**
     * 
     * 初始化db
     * @param string $dsn
     * @param string $username
     * @param string $password
     * @param string $charset default:utf8
     */
    function connect($rw, $host, $port, $username, $password, $charset = 'utf8') {
    		$dsn = self::buildDSN($host, $port);
    		if(isset(self::$db[$rw][$dsn])) return;
    		
            self::$db[$rw][$dsn] = new PDO($dsn, $username, $password);
            self::$db[$rw][$dsn]-> setAttribute(PDO::ATTR_ERRMODE, self::$errMode);
            self::$db[$rw][$dsn]-> setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
            self::$db[$rw][$dsn]->exec("SET character_set_connection={$charset}, character_set_results={$charset}, character_set_client=binary");  	
    }
    
    /**
     * 载入配置
     * @param array $config
     */
    public static function init($config) {
    	foreach($config['rw'] as $k=>$v) {
    		foreach($v as $vv) {
    			self::connect($k, $vv['host'], $vv['port'], $vv['username'], $vv['password']);
    		}
    	}
		
		if(isset($config['memcache'])) {
			self::$cache = true;
			CMemcache::init($config['memcache']);
		}
    }
    
    /**
     * 根据配置构造DSN
     * @param string $host
     * @param string $port
     * @return string DSN
     */
    function buildDSN($host = '127.0.0.1', $port = '3306') {
    	return 'mysql:host='.$host.';port='.$port;
    }
    
    /**
     * 正则匹配是否含有危险SQL语句
     * @param string $prepare 准备语句
     * @return mixed 如果有返回匹配到的危险语句 没有则返回false
     */
    function isDangerSQL($prepare) {
    	if(!is_array(self::$danger) || empty(self::$danger)) return;
    	
    	foreach(self::$danger as $v) {
    		preg_match("/{$v}/iUs", $prepare, $matches);
    		if(isset($matches[0])) return $matches[0];
    	}
    	return false;   
    }
    
    /**
     * 准备一个声明语句执行并且返回一个对象
     * @param string  $prepare SQL准备语句
     * @param boolen $denyDanger 是否拒绝执行危险的SQL 默认为true
     * @return object PDOStatement object
     */
    function prepare($prepare, $denyDanger = true, $rw = 'r') {
    	if($denyDanger) {
	    	$act = self::isDangerSQL($prepare);
	    	if($act!==false) {
	    		self::showMsg('非法操作:'.$act);
	    		exit;
	    	}
    	}
    	$dsn = self::getDSN($rw, $prepare);
    	//D($dsn);
    	return $dsn->prepare($prepare);
    }
    
    /**
     * 随机取一台
     */
    function getDSN($rw = 'r', $prepare) {
    	$c = count(self::$db[$rw]);
    	$dsns = array_keys(self::$db[$rw]);
    	if($c<2)
    		return self::$db[$rw][$dsns[0]];
    	
    	$n = hexdec(substr(md5($prepare), -5))%$c;
    	return self::$db[$rw][$dsns[$n]];
    }
    
    /**
     * 
     * 执行带返回结果的sql
     * @param string  $prepare sql准备语句
     * @param array  $values 数值数组
	 * @param int       $expire 缓存过期时间 单位为秒 默认不缓存
     * @param string  $fetchMethod 获取数据的方法 all|one 默认为all
     * @param string  $fetchMode 获取数据的模式 默认为PDO::FETCH_ASSOC
     * @param boolen $denyDanger 拒绝执行危险的SQL语句 默认为true 如:drop database
     * @return array  $res 结果集
     */
    public static function fetch($prepare, $values = array(), $expire=0, $fetchMethod = 'all', $fetchMode = PDO::FETCH_ASSOC, $denyDanger = true) {
		self::recodeLastSql($prepare);
		if(self::$cache && $mres = CMemcache::get($prepare)) {
			return $mres;
		}

        $stmt = self::prepare($prepare, $denyDanger);
        $stmt->execute($values);
		$stmt->setFetchMode($fetchMode);

		$res = array();
        if($fetchMethod == 'all')	$res = $stmt->fetchAll();
		elseif($fetchMethod == 'one')	$res = $stmt->fetch();
		
		if(self::$cache && $expire>0)
			CMemcache::set($prepare, $res, $expire);
        return $res;
    }
   
    /**
     * 
     * 执行不带返回结果的sql(insert,update,delete)
     * @param string  $prepare 准备语句
     * @param array  $values 数值数组
     * @param boolen $denyDanger 拒绝执行危险的SQL语句 默认为true 如:drop database
     */
    public static function execute($prepare, $values = array(), $denyDanger = true) {
    	self::recodeLastSql($prepare);
    	
    	$stmt = self::prepare($prepare, $denyDanger, 'w');
		return $stmt->execute($values);
    }
    
    /**
     * 
     * 获取最后一条sql
     */
    public static function getLastSql() {
    	return self::$lastSql;
    }
    
    /**
     * 
     * 记录最后一条sql
     * @param unknown_type $prepare
     */
    function recodeLastSql($prepare) {
    	if($prepare)	self::$lastSql = $prepare;
    }
    
    /**
     * 获取表引擎
     * 
     * @param String   $dbName 库名
     * @param String   $tableName 表名
     * @return String
     */
    public static function getTableEngine($dbName, $tableName) {
        $sql = "SHOW TABLE STATUS FROM {$dbName} WHERE Name='{$tableName}'";
        $arrayTableInfo = self::fetch($sql);
        return $arrayTableInfo[0]['Engine'];
    }
    
    /**
     * 事务开始
     */
    private static function beginTransaction() {
        self::$db->beginTransaction();
    }
    
    /**
     * 事务提交
     */
    private static function commit() {
        self::$db->commit();
    }
    
    /**
     * 事务回滚
     */
    private static function rollback() {
        self::$db->rollback();
    }
    
    /**
     * 通过事务处理多条SQL语句
     * 调用前需通过getTableEngine判断表引擎是否支持事务
     *
     * @param array $arraySql
     * @return boolean
     */
    public static function execTransaction($arraySql) {
        $retval = 1;
        self::beginTransaction();
        foreach ($arraySql as $sql) {
            if (self::execute($sql) == 0) $retval = 0;
        }
        if ($retval == 0) {
            self::rollback();
            return false;
        } else {
            self::commit();
            return true;
        }
    }

    /**
     * 获取指定数据表中的全部字段信息
     *
     * @param string $table 表名
     * @return array
     */
    private static function getFields($table) {
        return self::fetch("SHOW COLUMNS FROM {$table}");
    }
    
    /**
     * 捕获PDO错误信息
     */
    function getError() {
        if (self::$db->errorCode() != '00000') {
            $arrayError = self::$db->errorInfo();
           self::output($arrayError[2]);
        }
    }
    
    /**
     * 输出错误信息
     * 
     * @param String $msg
     */
    function output($msg) {
        //debug::output($msg);
		exit($msg);
    }
    
    /**
     * 输出提示信息
     * @param mixed $msg
     */
    function showMsg($msg) {
    	echo "<script>alert('{$msg}'); history.go(-1);</script>";
    	exit;
    }
    
    /**
     * 关闭数据库连接
     */
    public static function close() {
        self::$db = null;
    }
    
    
}
?>